﻿Imports Microsoft.VisualBasic
Imports System.Configuration.ConfigurationManager
Imports System.Data
Imports System.Data.SqlClient
Imports System

Public Class UsuarioDAO

    Dim cn As New SqlConnection(ConnectionStrings("cn").ConnectionString)

    Public Sub Agregar(ByVal usu As UsuarioBE)

        cn.Open()
        Try

            Dim cmd As New SqlCommand(
                "insert into tb_usuario (cod_usuario,   LogUser ,  pass , fec_registro,nom_empleado, " +
                    "ape_pat_empleado , ape_mat_empleado ) values (@cod,@loge,@pass,GETDATE(), @cod,@nom,@pat,@mat)", cn)


            With cmd.Parameters
                .Add("@cod", SqlDbType.VarChar).Value = usu.cod_usuario
                .Add("@loge", SqlDbType.VarChar).Value = usu.log_user
                .Add("@pass", SqlDbType.VarChar).Value = usu.pass_user
                .Add("@nom", SqlDbType.VarChar).Value = usu.nom_empleado
                .Add("@pat", SqlDbType.VarChar).Value = usu.ape_pat
                .Add("@mat", SqlDbType.VarChar).Value = usu.ape_mat
            End With

            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Codigo de usuario ya en uso, por favor ingrese un numero distinto")
        Finally
            cn.Close()
        End Try
    End Sub

    Public Sub Actualizar(ByVal usu As UsuarioBE)
        cn.Open()
        Try
            '
            Dim cmd As New SqlCommand(
                "update tb_usuario set LogUser = @loge, pass = @pass, fec_registro = @fec, " +
                "nom_empleado =@nom, ape_pat_empleado= @pat , ape_mat_empleado = @mat  where cod_usuario = @cod ", cn)


            With cmd.Parameters
                .Add("@cod", SqlDbType.VarChar).Value = usu.cod_usuario
                .Add("@pass", SqlDbType.VarChar).Value = usu.pass_user
                .Add("@loge", SqlDbType.VarChar).Value = usu.log_user
                .Add("@fec", SqlDbType.DateTime).Value = usu.fecha_reg
                .Add("@nom", SqlDbType.VarChar).Value = usu.nom_empleado
                .Add("@pat", SqlDbType.VarChar).Value = usu.ape_pat
                .Add("@mat", SqlDbType.VarChar).Value = usu.ape_mat


            End With

            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cn.Close()
        End Try

    End Sub

    Public Sub Eliminar(ByVal usu As UsuarioBE)
        cn.Open()
        Try

            Dim cmd As New SqlCommand(
                "delete from tb_usuario where cod_usuario = @cod ", cn)


            With cmd.Parameters
                .Add("@cod", SqlDbType.VarChar).Value = usu.cod_usuario
            End With

            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cn.Close()
        End Try

    End Sub


    Public Function Lista() As DataTable

        Dim da As New SqlDataAdapter(
              "select * from tb_usuario", cn)

        Dim tb As New DataTable
        da.Fill(tb)

        Return tb

    End Function


    Public Function Lista(ByVal clave As String) As DataTable

        Dim da As New SqlDataAdapter(
              "sp_busqueda_multiple_usuario", cn)
        da.SelectCommand.CommandType = CommandType.StoredProcedure
        With da.SelectCommand.Parameters
            .Add("@clave", SqlDbType.VarChar, 45).Value = clave
        End With

        Dim tb As New DataTable
        da.Fill(tb)

        Return tb

    End Function
End Class
